--------------------------------------------
-- Export file for user BNMS12            --
-- Created by xqz on 2013-11-22, 16:14:27 --
--------------------------------------------

-- spool portal_obj.log

-- prompt
-- prompt Creating table TB_SYS_4A_OPER_LOG
-- prompt =================================
-- prompt
create table TB_SYS_4A_OPER_LOG
(
  LOGIN_ID     VARCHAR2(500),
  OPERTIME     DATE,
  DESCR        VARCHAR2(1000),
  OPERID       VARCHAR2(200),
  OPERTYPE     VARCHAR2(100),
  RESPONSE_STR VARCHAR2(4000),
  REQUEST_IP   VARCHAR2(500),
  INTERFACE_ID VARCHAR2(50),
  REQUEST_STR  CLOB,
  LOG_ID       NUMBER(10)
);
comment on table TB_SYS_4A_OPER_LOG
  is '4a 操作bomc账号日志表';
comment on column TB_SYS_4A_OPER_LOG.LOGIN_ID
  is '被操作bomc账号id';
comment on column TB_SYS_4A_OPER_LOG.OPERTIME
  is '时间';
comment on column TB_SYS_4A_OPER_LOG.DESCR
  is '描述';
comment on column TB_SYS_4A_OPER_LOG.OPERID
  is '操作人（4a侧操作id）';
comment on column TB_SYS_4A_OPER_LOG.OPERTYPE
  is '操作类型';
comment on column TB_SYS_4A_OPER_LOG.RESPONSE_STR
  is '响应报文';
comment on column TB_SYS_4A_OPER_LOG.REQUEST_IP
  is '请求ip';
comment on column TB_SYS_4A_OPER_LOG.INTERFACE_ID
  is 'IF6：部门信息变更接口；IF53：角色信息变更接口； IF54：模块（权限信息）变更接口；IF51：人员角色信息变更接口；IF55：角色模块（权限）信息变更接口；';
comment on column TB_SYS_4A_OPER_LOG.REQUEST_STR
  is '请求报文';
comment on column TB_SYS_4A_OPER_LOG.LOG_ID
  is 'id';

-- prompt
-- prompt Creating table TB_SYS_CALENDAR
-- prompt ==============================
-- prompt
create table TB_SYS_CALENDAR
(
  ID          VARCHAR2(10),
  TITLE       VARCHAR2(50) not null,
  START_TIME  DATE,
  END_TIME    DATE,
  ALLDAY      VARCHAR2(1),
  DESCRIPTION VARCHAR2(200),
  NOTICE      VARCHAR2(1) default 2,
  LOGIN_ID    VARCHAR2(20) not null
);
comment on column TB_SYS_CALENDAR.TITLE
  is '任务标题';
comment on column TB_SYS_CALENDAR.ALLDAY
  is '是否是全天任务';
comment on column TB_SYS_CALENDAR.DESCRIPTION
  is '任务描述';
comment on column TB_SYS_CALENDAR.NOTICE
  is '提醒方式：0为不提醒，1为消息中心2为短信4为邮件';
comment on column TB_SYS_CALENDAR.LOGIN_ID
  is '所属人';

-- prompt
-- prompt Creating table TB_SYS_COMPANY
-- prompt =============================
-- prompt
create table TB_SYS_COMPANY
(
  COMP_ID        VARCHAR2(10) not null,
  COMP_NAME      VARCHAR2(50),
  COMP_DESC      VARCHAR2(255),
  COMP_CODE      VARCHAR2(10),
  COMP_TYPE      VARCHAR2(1),
  MANAGE_UNIT_ID VARCHAR2(20)
);

-- prompt
-- prompt Creating table TB_SYS_DEPT
-- prompt ==========================
-- prompt
create table TB_SYS_DEPT
(
  DEPT_ID        VARCHAR2(36) not null,
  DEPT_NAME      VARCHAR2(40),
  DEPT_DESC      VARCHAR2(255),
  DEPT_CODE      VARCHAR2(20),
  COMP_ID        VARCHAR2(10),
  MGR_ID         VARCHAR2(20),
  SUPER_DEPT_ID  VARCHAR2(36),
  AREA           VARCHAR2(1),
  IS_TEAM        VARCHAR2(1),
  MANAGE_UNIT_ID VARCHAR2(20),
  DEPT_ID_BAK    NUMBER
);
comment on table TB_SYS_DEPT
  is '系统部门信息表';
comment on column TB_SYS_DEPT.DEPT_ID
  is '部门ID唯一ID';
comment on column TB_SYS_DEPT.DEPT_NAME
  is '部门名称';
comment on column TB_SYS_DEPT.DEPT_DESC
  is '部门描述';
comment on column TB_SYS_DEPT.DEPT_CODE
  is '部门编码';
comment on column TB_SYS_DEPT.COMP_ID
  is '暂不使用';
comment on column TB_SYS_DEPT.MGR_ID
  is '部门负责人';
comment on column TB_SYS_DEPT.SUPER_DEPT_ID
  is '父部门ID';
comment on column TB_SYS_DEPT.AREA
  is '暂不使用';
comment on column TB_SYS_DEPT.IS_TEAM
  is '暂不使用';
comment on column TB_SYS_DEPT.MANAGE_UNIT_ID
  is '暂不使用';
comment on column TB_SYS_DEPT.DEPT_ID_BAK
  is '暂不使用';

-- prompt
-- prompt Creating table TB_SYS_GRPMEMBER
-- prompt ===============================
-- prompt
create table TB_SYS_GRPMEMBER
(
  USERID  NUMBER not null,
  GROUPID NUMBER not null
);

-- prompt
-- prompt Creating table TB_SYS_LINK
-- prompt ==========================
-- prompt
create table TB_SYS_LINK
(
  LINK_ID    VARCHAR2(50),
  LINK_NAME  VARCHAR2(20),
  LINK_URL   VARCHAR2(1024),
  LINK_ICON  VARCHAR2(1024),
  LINK_TYPE  VARCHAR2(20),
  LINK_PROPS VARCHAR2(50),
  LINK_ORDER NUMBER(10)
);
comment on column TB_SYS_LINK.LINK_ID
  is 'LINK主键';
comment on column TB_SYS_LINK.LINK_NAME
  is 'LINK名称';
comment on column TB_SYS_LINK.LINK_URL
  is 'LINK指向的URL';
comment on column TB_SYS_LINK.LINK_ICON
  is 'LINK图标的URL';
comment on column TB_SYS_LINK.LINK_TYPE
  is 'LINK类别';
comment on column TB_SYS_LINK.LINK_PROPS
  is 'LINK的一些其他属性，用,分隔';
comment on column TB_SYS_LINK.LINK_ORDER
  is 'LINK显示的顺序';

-- prompt
-- prompt Creating table TB_SYS_LOG
-- prompt =========================
-- prompt
create table TB_SYS_LOG
(
  LOGIN_ID        VARCHAR2(20),
  OPERATE_TIME    DATE,
  OPERATE_CONTENT VARCHAR2(1000),
  OPERATEFLAG     CHAR(1),
  ID              VARCHAR2(100) not null
);

-- prompt
-- prompt Creating table TB_SYS_LOGGING
-- prompt =============================
-- prompt
create table TB_SYS_LOGGING
(
  ID             VARCHAR2(50) not null,
  LOGIN_ID       VARCHAR2(20),
  OPERATE_TIME   DATE,
  OPERATE_CLASS  VARCHAR2(100),
  OPERATE_METHOD VARCHAR2(100),
  OPERATE_ARGS   VARCHAR2(2000),
  RAW_DATA       VARCHAR2(2000)
);
comment on column TB_SYS_LOGGING.ID
  is '唯一标示';
comment on column TB_SYS_LOGGING.LOGIN_ID
  is '动作操作用户ID';
comment on column TB_SYS_LOGGING.OPERATE_TIME
  is '操作时间';
comment on column TB_SYS_LOGGING.OPERATE_CLASS
  is '执行类';
comment on column TB_SYS_LOGGING.OPERATE_METHOD
  is '执行方法';
comment on column TB_SYS_LOGGING.OPERATE_ARGS
  is '执行数据';
comment on column TB_SYS_LOGGING.RAW_DATA
  is '执行前原始数据';

-- prompt
-- prompt Creating table TB_SYS_LOGGING_CONFIG
-- prompt ====================================
-- prompt
create table TB_SYS_LOGGING_CONFIG
(
  AOP_CLASS  VARCHAR2(100),
  AOP_METHOD VARCHAR2(100),
  TAB_ID     VARCHAR2(50),
  DATA_TAB   VARCHAR2(50),
  PARAM_ID   VARCHAR2(50)
);
comment on column TB_SYS_LOGGING_CONFIG.AOP_CLASS
  is 'AOP切面类';
comment on column TB_SYS_LOGGING_CONFIG.AOP_METHOD
  is 'AOP切面方法';
comment on column TB_SYS_LOGGING_CONFIG.TAB_ID
  is '备份数据表主键ID';
comment on column TB_SYS_LOGGING_CONFIG.DATA_TAB
  is '备份数据表';
comment on column TB_SYS_LOGGING_CONFIG.PARAM_ID
  is '备份数据表主键ID对应的方法参数名';

-- prompt
-- prompt Creating table TB_SYS_LOGINMSG
-- prompt ==============================
-- prompt
create table TB_SYS_LOGINMSG
(
  LOGIN_ID        VARCHAR2(20) not null,
  NAME            VARCHAR2(40),
  LOGIN_PASSWD    VARCHAR2(100),
  LOGIN_PHONE     VARCHAR2(13),
  LOGIN_STATE     NUMBER(15),
  COMP_ID         VARCHAR2(10),
  DEPT_ID         VARCHAR2(36),
  EMAIL_ADDR      VARCHAR2(40),
  LOCKSTATE       CHAR(1),
  USER_ID         NUMBER(8),
  LOCAL_PHONE     VARCHAR2(20),
  SECOND_PHONE    VARCHAR2(20),
  FROM4A_LOGIN_ID VARCHAR2(20),
  MENU_TYPE       VARCHAR2(2)
);
comment on column TB_SYS_LOGINMSG.MENU_TYPE
  is 'portal菜单样式';

-- prompt
-- prompt Creating table TB_SYS_LOG_MODULE
-- prompt ================================
-- prompt
create table TB_SYS_LOG_MODULE
(
  ID           VARCHAR2(36) not null,
  LOGIN_ID     VARCHAR2(20) not null,
  OPERATE_TIME DATE not null,
  MODULE_ID    VARCHAR2(36) not null,
  CLIENT_IP    VARCHAR2(20)
)
;
comment on column TB_SYS_LOG_MODULE.CLIENT_IP
  is '客户端ip地址';

-- prompt
-- prompt Creating table TB_SYS_MANAGE_UNIT
-- prompt =================================
-- prompt
create table TB_SYS_MANAGE_UNIT
(
  MANAGE_UNIT_ID        VARCHAR2(20) not null,
  MANAGE_UNIT_NAME      VARCHAR2(100),
  MANAGE_UNIT_DESC      VARCHAR2(400),
  PARENT_MANAGE_UNIT_ID VARCHAR2(20),
  ENABLE                VARCHAR2(1)
);

-- prompt
-- prompt Creating table TB_SYS_MESSAGE
-- prompt =============================
-- prompt
create table TB_SYS_MESSAGE
(
  MSG_ID       VARCHAR2(50) not null,
  MSG_SENDER   VARCHAR2(20),
  MSG_CONTENT  VARCHAR2(1024),
  MSG_SOURCE   VARCHAR2(20),
  MSG_TYPE     VARCHAR2(20),
  DETAILS_PATH VARCHAR2(1024),
  SEND_TIME    DATE,
  RESPONSE_ID  VARCHAR2(32),
  ONLINE_ID    VARCHAR2(32),
  DISPLAY_SUM  INTEGER default 0
);
comment on column TB_SYS_MESSAGE.MSG_ID
  is '消息主键, 通过序列SQ_SYS_MESSAGE取值';
comment on column TB_SYS_MESSAGE.MSG_SENDER
  is '消息发送人';
comment on column TB_SYS_MESSAGE.MSG_CONTENT
  is '消息内容';
comment on column TB_SYS_MESSAGE.MSG_SOURCE
  is '消息来源';
comment on column TB_SYS_MESSAGE.MSG_TYPE
  is '消息类型';
comment on column TB_SYS_MESSAGE.DETAILS_PATH
  is '详细情况的链接地址';
comment on column TB_SYS_MESSAGE.SEND_TIME
  is '消息发送时间';
alter table TB_SYS_MESSAGE
  add constraint PK_TB_SYS_MESSAGE primary key (MSG_ID);

-- prompt
-- prompt Creating table TB_SYS_MESSAGE_SETTING
-- prompt =====================================
-- prompt
create table TB_SYS_MESSAGE_SETTING
(
  GUID             VARCHAR2(64),
  LOGIN_ID         VARCHAR2(64),
  DISPLAY_TYPE     INTEGER,
  DISPLAY_INTERVAL INTEGER,
  DISPLAY_SUM      INTEGER,
  DISPLAY_SCOPE    INTEGER
);

-- prompt
-- prompt Creating table TB_SYS_MESSAGE_USER
-- prompt ==================================
-- prompt
create table TB_SYS_MESSAGE_USER
(
  MSG_ID    VARCHAR2(50) not null,
  LOGIN_ID  VARCHAR2(20) not null,
  READ_TIME DATE
);
comment on table TB_SYS_MESSAGE_USER
  is '消息与接收人关联表';
comment on column TB_SYS_MESSAGE_USER.MSG_ID
  is '消息主键';
comment on column TB_SYS_MESSAGE_USER.LOGIN_ID
  is '消息接收者';
comment on column TB_SYS_MESSAGE_USER.READ_TIME
  is '首次阅读时间';
alter table TB_SYS_MESSAGE_USER
  add constraint PK_TB_SYS_MESSAGE_USER primary key (MSG_ID, LOGIN_ID)
;

-- prompt
-- prompt Creating table TB_SYS_MODULE
-- prompt ============================
-- prompt
create table TB_SYS_MODULE
(
  MODULE_ID        VARCHAR2(36) not null,
  MODULE_NAME      VARCHAR2(100),
  MODULE_PATH      VARCHAR2(1000),
  MODULE_ENABLE    NUMBER(15),
  MODULE_TARGET    VARCHAR2(20),
  MODULE_LAYOUT    VARCHAR2(1),
  IS_REFRESH       VARCHAR2(1),
  OPEN_MODULE_ID   VARCHAR2(20),
  IS_VIEW          VARCHAR2(1),
  PAGE_ELEMENT     VARCHAR2(60),
  VIEW_ORDER       VARCHAR2(10),
  MODULE_LEVEL     INTEGER,
  PARENT_ID        VARCHAR2(36),
  IS_LEAF          VARCHAR2(2),
  MODULE_DIRECTORY VARCHAR2(500)
)
;
comment on column TB_SYS_MODULE.IS_LEAF
  is '1叶子节点，其他为非叶子节点';
comment on column TB_SYS_MODULE.MODULE_DIRECTORY
  is '模块目录';

-- prompt
-- prompt Creating table TB_SYS_PANEL_CONFIG
-- prompt ==================================
-- prompt
create table TB_SYS_PANEL_CONFIG
(
  PANEL_ID     VARCHAR2(20),
  PANEL_NAME   VARCHAR2(64),
  PANEL_PATH   VARCHAR2(200),
  PANEL_HEIGHT VARCHAR2(20)
);

-- prompt
-- prompt Creating table TB_SYS_PARA
-- prompt ==========================
-- prompt
create table TB_SYS_PARA
(
  PARA_NAME  VARCHAR2(100),
  PARA_DESC  VARCHAR2(200),
  ENABLE     VARCHAR2(1),
  PARA_ID    VARCHAR2(50) not null,
  PARA_VALUE VARCHAR2(200)
);
alter table TB_SYS_PARA
  add constraint PARA_ID primary key (PARA_ID)
 ;

-- prompt
-- prompt Creating table TB_SYS_PORTAL_HELP
-- prompt =================================
-- prompt
create table TB_SYS_PORTAL_HELP
(
  GUID        VARCHAR2(64),
  LOGIN_ID    VARCHAR2(32),
  HELP_ID     VARCHAR2(64),
  HELP_MSG    VARCHAR2(2000),
  CREATE_DATE DATE
)
;

-- prompt
-- prompt Creating table TB_SYS_PORTAL_LAYOUT
-- prompt ===================================
-- prompt
create table TB_SYS_PORTAL_LAYOUT
(
  LAYOUT_ID     VARCHAR2(50) not null,
  LAYOUT_NAME   VARCHAR2(50),
  IMAGE         VARCHAR2(1024),
  COLUMNS       NUMBER(1),
  COLUMN_WIDTHS VARCHAR2(100)
);
comment on table TB_SYS_PORTAL_LAYOUT
  is '保存个人门户的布局模板';
comment on column TB_SYS_PORTAL_LAYOUT.LAYOUT_ID
  is '布局标识';
comment on column TB_SYS_PORTAL_LAYOUT.LAYOUT_NAME
  is '布局名称';
comment on column TB_SYS_PORTAL_LAYOUT.IMAGE
  is '布局的图标URI';
comment on column TB_SYS_PORTAL_LAYOUT.COLUMNS
  is '列数';
comment on column TB_SYS_PORTAL_LAYOUT.COLUMN_WIDTHS
  is '各列的宽度百分比';
alter table TB_SYS_PORTAL_LAYOUT
  add constraint PK_TB_SYS_PORTAL_LAYOUT primary key (LAYOUT_ID)
;

-- prompt
-- prompt Creating table TB_SYS_PORTAL_PORLET
-- prompt ===================================
-- prompt
create table TB_SYS_PORTAL_PORLET
(
  PORLET_ID   VARCHAR2(50) not null,
  TITLE       VARCHAR2(50),
  HEIGHT      VARCHAR2(5),
  CONTENT_URL VARCHAR2(2048),
  MAX_URL     VARCHAR2(1024),
  DESCRIPTION VARCHAR2(1024),
  SECOND      VARCHAR2(2)
);
comment on table TB_SYS_PORTAL_PORLET
  is '个人门户的模块';
comment on column TB_SYS_PORTAL_PORLET.PORLET_ID
  is '模块标识';
comment on column TB_SYS_PORTAL_PORLET.TITLE
  is '标题';
comment on column TB_SYS_PORTAL_PORLET.HEIGHT
  is '模块的预设高度';
comment on column TB_SYS_PORTAL_PORLET.CONTENT_URL
  is '模块内容的URL';
comment on column TB_SYS_PORTAL_PORLET.MAX_URL
  is '最大化模块时的内容页面的URL';
comment on column TB_SYS_PORTAL_PORLET.DESCRIPTION
  is '模块描述';
comment on column TB_SYS_PORTAL_PORLET.SECOND
  is '切换url的间隔时间 秒';
alter table TB_SYS_PORTAL_PORLET
  add constraint PK_TB_SYS_PORTAL_PORLET primary key (PORLET_ID)
;

-- prompt
-- prompt Creating table TB_SYS_PORTAL_ROLE
-- prompt =================================
-- prompt
create table TB_SYS_PORTAL_ROLE
(
  ROLE_ID   VARCHAR2(20) not null,
  PORLET_ID VARCHAR2(50) not null,
  OBLIGED   VARCHAR2(1)
);
comment on table TB_SYS_PORTAL_ROLE
  is '角色与模块的对应关系';
comment on column TB_SYS_PORTAL_ROLE.PORLET_ID
  is '模块标识';
comment on column TB_SYS_PORTAL_ROLE.OBLIGED
  is '是否强制显示（不能关闭），1为true，0为false';
alter table TB_SYS_PORTAL_ROLE
  add constraint PK_TB_SYS_PORTAL_ROLE primary key (ROLE_ID, PORLET_ID)
;

-- prompt
-- prompt Creating table TB_SYS_PORTAL_USERSETTING
-- prompt ========================================
-- prompt
create table TB_SYS_PORTAL_USERSETTING
(
  LOGIN_ID  VARCHAR2(20) not null,
  LAYOUT_ID VARCHAR2(50) not null,
  PORLETS   VARCHAR2(1024)
);
comment on table TB_SYS_PORTAL_USERSETTING
  is '记录用户的个人门户配置';
comment on column TB_SYS_PORTAL_USERSETTING.LAYOUT_ID
  is '布局标识';
comment on column TB_SYS_PORTAL_USERSETTING.PORLETS
  is '用户选择的模块，模块用逗号分隔，列之间用分号分隔';

-- prompt
-- prompt Creating table TB_SYS_POSTTIME
-- prompt ==============================
-- prompt
create table TB_SYS_POSTTIME
(
  LOGIN_ID    VARCHAR2(20),
  START_TIME  DATE,
  END_TIME    DATE,
  EMPLOYEE_ID VARCHAR2(20),
  ID          VARCHAR2(100) not null
);

-- prompt
-- prompt Creating table TB_SYS_ROLE
-- prompt ==========================
-- prompt
create table TB_SYS_ROLE
(
  ROLE_ID        VARCHAR2(20) not null,
  ROLE_NAME      VARCHAR2(40) not null,
  ROLE_DESC      VARCHAR2(255),
  GRANT_ENABLE   VARCHAR2(1),
  ROLE_SEARCH_ID VARCHAR2(30),
  MANAGE_UNIT_ID VARCHAR2(20),
  PARENT_ID      VARCHAR2(20) default 0,
  DOMAIN_ID      VARCHAR2(20)
);
comment on column TB_SYS_ROLE.ROLE_ID
  is '角色编号';
comment on column TB_SYS_ROLE.ROLE_NAME
  is '角色名称';
comment on column TB_SYS_ROLE.ROLE_DESC
  is '角色描述';
comment on column TB_SYS_ROLE.PARENT_ID
  is '上级角色编号';

-- prompt
-- prompt Creating table TB_SYS_ROLE_DEPTID
-- prompt =================================
-- prompt
create table TB_SYS_ROLE_DEPTID
(
  ROLE_ID   VARCHAR2(255),
  DEPT_TYPE VARCHAR2(255)
);

-- prompt
-- prompt Creating table TB_SYS_ROLE_MANAGERUNITID
-- prompt ========================================
-- prompt
create table TB_SYS_ROLE_MANAGERUNITID
(
  ROLE_ID        VARCHAR2(20) not null,
  MANAGE_UNIT_ID VARCHAR2(20)
);

-- prompt
-- prompt Creating table TB_SYS_ROLE_MODULE
-- prompt =================================
-- prompt
create table TB_SYS_ROLE_MODULE
(
  MODULE_ID VARCHAR2(36) not null,
  ROLE_ID   VARCHAR2(36)
);

-- prompt
-- prompt Creating table TB_SYS_ROLE_PORTAL
-- prompt =================================
-- prompt
create table TB_SYS_ROLE_PORTAL
(
  ROLE_IDS      VARCHAR2(200),
  PANEL_COLLECT VARCHAR2(200),
  SUBMIT_TIME   DATE,
  PORTAL_ORDER  VARCHAR2(20),
  ROLE_NAMES    VARCHAR2(400),
  ENABLE        VARCHAR2(1)
);

-- prompt
-- prompt Creating table TB_SYS_STYLE
-- prompt ===========================
-- prompt
create table TB_SYS_STYLE
(
  STYLE_ID   VARCHAR2(50),
  STYLE_NAME VARCHAR2(100),
  STYLE_DESC VARCHAR2(200),
  STYLE_PATH VARCHAR2(100),
  ENABLE     VARCHAR2(1),
  IS_DEFAULT VARCHAR2(1),
  IS_TAB     VARCHAR2(1) default 0
);
-- prompt
-- prompt Creating table TB_SYS_STYLE_PARA
-- prompt ================================
-- prompt
create table TB_SYS_STYLE_PARA
(
  STYLE_ID  VARCHAR2(50),
  PARA_NAME VARCHAR2(100),
  PARA_DESC VARCHAR2(200),
  ENABLE    VARCHAR2(1),
  PARA_ID   VARCHAR2(50)
);

-- prompt
-- prompt Creating table TB_SYS_SUB_SYSTEM
-- prompt ================================
-- prompt
create table TB_SYS_SUB_SYSTEM
(
  SYSTEM_ID     VARCHAR2(30),
  SYSTEM_NAME   VARCHAR2(60),
  SYSTEM_PATH   VARCHAR2(512),
  ENABLE        VARCHAR2(1),
  VIEW_ORDER    VARCHAR2(20),
  SYSTEM_LAYOUT VARCHAR2(1)
);

-- prompt
-- prompt Creating table TB_SYS_USERINFO
-- prompt ==============================
-- prompt
create table TB_SYS_USERINFO
(
  ID            NUMBER not null,
  ACCOUNT       VARCHAR2(64) not null,
  PASSWORD      VARCHAR2(64) not null,
  NAME          VARCHAR2(64),
  MOBILE        VARCHAR2(64),
  EMAIL         VARCHAR2(64),
  STATUS        NUMBER,
  CREATETIME    TIMESTAMP(6),
  CREATEUSER    NUMBER,
  DATAAUTHORITY VARCHAR2(255)
);
-- prompt
-- prompt Creating table TB_SYS_USERPANEL_ORDER
-- prompt =====================================
-- prompt
create table TB_SYS_USERPANEL_ORDER
(
  LOGIN_ID    VARCHAR2(50) not null,
  PANEL_ID    VARCHAR2(50) not null,
  PANEL_ORDER VARCHAR2(2) not null,
  GROUPNAME   VARCHAR2(50)
);
comment on column TB_SYS_USERPANEL_ORDER.LOGIN_ID
  is '用户名';
comment on column TB_SYS_USERPANEL_ORDER.PANEL_ID
  is 'panel id';
comment on column TB_SYS_USERPANEL_ORDER.PANEL_ORDER
  is 'panel显示顺序';
comment on column TB_SYS_USERPANEL_ORDER.GROUPNAME
  is '列的class名称';

-- prompt
-- prompt Creating table TB_SYS_USER_MODULE_LOG
-- prompt =====================================
-- prompt
create table TB_SYS_USER_MODULE_LOG
(
  LOGIN_ID    VARCHAR2(20) not null,
  MODULE_ID   VARCHAR2(20) not null,
  ACCESS_TIME DATE
);

-- prompt
-- prompt Creating table TB_SYS_USER_PORTAL
-- prompt =================================
-- prompt
create table TB_SYS_USER_PORTAL
(
  LOGIN_ID      VARCHAR2(20),
  PANEL_COLLECT VARCHAR2(200),
  SUBMIT_TIME   DATE
);

-- prompt
-- prompt Creating table TB_SYS_USER_PRIVILEGE
-- prompt ====================================
-- prompt
create table TB_SYS_USER_PRIVILEGE
(
  LOGIN_ID       VARCHAR2(20),
  MANAGE_UNIT_ID VARCHAR2(20),
  ENABLE         VARCHAR2(1),
  PRIVILEGE_ID   VARCHAR2(50)
);

-- prompt
-- prompt Creating table TB_SYS_USER_ROLE
-- prompt ===============================
-- prompt
create table TB_SYS_USER_ROLE
(
  LOGIN_ID VARCHAR2(20) not null,
  OP_TIME  DATE,
  ROLE_ID  VARCHAR2(20) not null
);

-- prompt
-- prompt Creating table TB_SYS_USER_SHORTCUT
-- prompt ===================================
-- prompt
create table TB_SYS_USER_SHORTCUT
(
  LOGIN_ID      VARCHAR2(20) not null,
  SHORTCUT_NAME VARCHAR2(64),
  SUBMIT_TIME   DATE,
  SHORTCUT_PATH VARCHAR2(200) not null,
  MODULE_ID     VARCHAR2(36),
  TYPE_ID       VARCHAR2(37),
  SHORTCUT_ID   VARCHAR2(37)
);
alter table TB_SYS_USER_SHORTCUT
  add constraint SHORTCUT_PATH primary key (SHORTCUT_PATH, LOGIN_ID)
 ;
-- prompt
-- prompt Creating table TB_SYS_USER_SHORTCUT_TYPE
-- prompt ========================================
-- prompt
create table TB_SYS_USER_SHORTCUT_TYPE
(
  TYPE_ID     VARCHAR2(37),
  TYPE_NAME   VARCHAR2(100),
  LOGIN_ID    VARCHAR2(37),
  CREATE_TIME DATE
);
-- prompt
-- prompt Creating table TB_SYS_WORKPLAN
-- prompt ==============================
-- prompt
create table TB_SYS_WORKPLAN
(
  TASK_ID         VARCHAR2(50),
  TASK_NAME       VARCHAR2(50),
  TASK_DESC       VARCHAR2(1024),
  START_TIME      DATE,
  END_TIME        DATE,
  TASK_STATUS     VARCHAR2(2) default 0,
  COMPLETION_TIME DATE,
  COMPLETION_DESC VARCHAR2(1024),
  PARENT_ID       VARCHAR2(50),
  LOGIN_ID        VARCHAR2(20),
  MILESTONE       VARCHAR2(2) default 0,
  ALARM_MODE      VARCHAR2(2) default 1,
  ALARM_MINUTE    VARCHAR2(10) default 1
);
comment on table TB_SYS_WORKPLAN
  is '个人工作计划';
comment on column TB_SYS_WORKPLAN.TASK_NAME
  is '任务名称';
comment on column TB_SYS_WORKPLAN.TASK_DESC
  is '任务描述';
comment on column TB_SYS_WORKPLAN.START_TIME
  is '任务开始时间';
comment on column TB_SYS_WORKPLAN.END_TIME
  is '计划任务完成时间';
comment on column TB_SYS_WORKPLAN.TASK_STATUS
  is '任务状态,0表示未完成，1表示已完成，-1表示已取消';
comment on column TB_SYS_WORKPLAN.COMPLETION_TIME
  is '任务实际完成时间';
comment on column TB_SYS_WORKPLAN.COMPLETION_DESC
  is '任务完成描述';
comment on column TB_SYS_WORKPLAN.LOGIN_ID
  is '用户ID';
comment on column TB_SYS_WORKPLAN.MILESTONE
  is '是否为里程碑节点,1为是，否则不是';
comment on column TB_SYS_WORKPLAN.ALARM_MODE
  is '提醒方式，默认1代表消息中心提醒，2代表短信提醒，3=1+2，4代表邮件提醒， 5=1+4， 6=2+4， 7=1+2+4';
comment on column TB_SYS_WORKPLAN.ALARM_MINUTE
  is '提前提醒时常（分），如60表示提前1小时提醒';

-- prompt
-- prompt Creating table T_SYSTEM_SERVER
-- prompt ==============================
-- prompt
create table T_SYSTEM_SERVER
(
  SERVERNAME    VARCHAR2(200),
  SERVERIP      VARCHAR2(50) not null,
  SERVERPORT    VARCHAR2(50),
  SERVERCONTEXT VARCHAR2(50),
  SERVERID      VARCHAR2(50) not null,
  SERVERDESC    VARCHAR2(2000),
  LASTUPTIME    DATE,
  LASTDOWNTIME  DATE,
  STATUS        VARCHAR2(2) not null
);
comment on column T_SYSTEM_SERVER.SERVERNAME
  is '服务器名称';
comment on column T_SYSTEM_SERVER.SERVERIP
  is '服务器IP';
comment on column T_SYSTEM_SERVER.SERVERPORT
  is '服务器端口';
comment on column T_SYSTEM_SERVER.SERVERCONTEXT
  is '服务器路径';
comment on column T_SYSTEM_SERVER.SERVERID
  is '服务器编号';
comment on column T_SYSTEM_SERVER.SERVERDESC
  is '服务器描述';
comment on column T_SYSTEM_SERVER.LASTUPTIME
  is '服务器最近启动时间';
comment on column T_SYSTEM_SERVER.LASTDOWNTIME
  is '服务器最近关闭时间';
comment on column T_SYSTEM_SERVER.STATUS
  is '服务器状态';
alter table T_SYSTEM_SERVER
  add constraint PK_SYSTEM_SERVER_ID primary key (SERVERID)
;


alter table TB_SYS_LOGINMSG modify DEPT_ID varchar2(36);





alter table TB_SYS_MODULE modify MODULE_ID VARCHAR2(36);


alter table TB_SYS_PORTAL_PORLET modify CONTENT_URL varchar2(2048);

alter table TB_SYS_ROLE_MODULE modify MODULE_ID varchar2(36);
alter table TB_SYS_ROLE_MODULE modify ROLE_ID varchar2(36);


create table TB_SYS_WORKGROUP
(
  workgroup_id   VARCHAR2(100) not null,
  workgroup_type VARCHAR2(20),
  workgroup_name VARCHAR2(100),
  workgroup_desc VARCHAR2(255),
  lockstate      CHAR(1),
  super_wg       VARCHAR2(100),
  line_level     VARCHAR2(2),
  auth_level     VARCHAR2(2),
  conf_id        VARCHAR2(50),
  deptid         VARCHAR2(25),
  sortid         VARCHAR2(25)
);
-- Add comments to the columns 
comment on column TB_SYS_WORKGROUP.workgroup_id
  is 'id';
comment on column TB_SYS_WORKGROUP.workgroup_type
  is '工作组类型';
comment on column TB_SYS_WORKGROUP.workgroup_name
  is '工作组名称';
comment on column TB_SYS_WORKGROUP.workgroup_desc
  is '说明';
comment on column TB_SYS_WORKGROUP.lockstate
  is '状态 1锁定';
comment on column TB_SYS_WORKGROUP.super_wg
  is '父节点id';
comment on column TB_SYS_WORKGROUP.line_level
  is '预留';
comment on column TB_SYS_WORKGROUP.auth_level
  is '预留';
comment on column TB_SYS_WORKGROUP.conf_id
  is '预留';
comment on column TB_SYS_WORKGROUP.deptid
  is '系统id';
comment on column TB_SYS_WORKGROUP.sortid
  is '排序';
  create table TB_SYS_WORKGROUP_DEPT
(
  workgroup_id VARCHAR2(36),
  dept_id      VARCHAR2(50),
  user_id      VARCHAR2(50)
);
create table TB_SYS_LOGGING_CFG
(
  operate_class    VARCHAR2(100) not null,
  operate_method   VARCHAR2(100) not null,
  cn_name          VARCHAR2(50),
  enable           VARCHAR2(1),
  title_expression VARCHAR2(500)
);
-- Add comments to the columns 
comment on column TB_SYS_LOGGING_CFG.operate_class
  is '执行类';
comment on column TB_SYS_LOGGING_CFG.operate_method
  is '执行方法';
comment on column TB_SYS_LOGGING_CFG.cn_name
  is '操作名称';
comment on column TB_SYS_LOGGING_CFG.enable
  is '1可用；非1不可用';
comment on column TB_SYS_LOGGING_CFG.title_expression
  is '显示内容的表达式';
  
  create table TB_SYS_ADMINROLE
(
  role_id   VARCHAR2(20) not null,
  role_name VARCHAR2(100)
);
insert into TB_SYS_LOGGING_CFG (operate_class, operate_method, cn_name, enable, title_expression)
values ('com.sitech.portal.server.service.impl.CalendarServiceImpl', 'deleteCalendarEvent', '删除日历内容', null, null);
insert into TB_SYS_LOGGING_CFG (operate_class, operate_method, cn_name, enable, title_expression)
values ('com.sitech.portal.server.service.impl.MessageSettingServiceImpl', 'updateMessageSetting', '修改消息设置', null, null);
insert into TB_SYS_LOGGING_CFG (operate_class, operate_method, cn_name, enable, title_expression)
values ('com.sitech.portal.server.service.impl.PortalServiceImpl', 'updateUserSetting_PORLETS', '修改用户设置', null, null);
insert into TB_SYS_LOGGING_CFG (operate_class, operate_method, cn_name, enable, title_expression)
values ('com.sitech.portal.server.service.impl.PortalServiceImpl', 'deletePORLETById', null, null, null);
insert into TB_SYS_LOGGING_CFG (operate_class, operate_method, cn_name, enable, title_expression)
values ('com.sitech.portal.server.service.impl.PortalServiceImpl', 'updatePORLET', null, null, null);
insert into TB_SYS_LOGGING_CFG (operate_class, operate_method, cn_name, enable, title_expression)
values ('com.sitech.portal.server.service.impl.PortalServiceImpl', 'updateUserSetting_LAYOUT', '修改布局', null, null);
insert into TB_SYS_LOGGING_CFG (operate_class, operate_method, cn_name, enable, title_expression)
values ('com.sitech.portal.server.service.impl.PortalServiceImpl', 'updateRoleMapOfPorlet', null, null, null);
insert into TB_SYS_LOGGING_CFG (operate_class, operate_method, cn_name, enable, title_expression)
values ('com.sitech.portal.server.service.impl.ShortcutServiceImpl', 'deleteType', '删除快捷菜单类别', null, null);
insert into TB_SYS_LOGGING_CFG (operate_class, operate_method, cn_name, enable, title_expression)
values ('com.sitech.portal.server.service.impl.ShortcutServiceImpl', 'delete', '删除快捷菜单', null, null);
insert into TB_SYS_LOGGING_CFG (operate_class, operate_method, cn_name, enable, title_expression)
values ('com.sitech.portal.system.service.impl.SysDeptServiceImpl', 'update', '修改部门信息', '1', 'before.DEPT_NAME-->after.DEPT_NAME');
insert into TB_SYS_LOGGING_CFG (operate_class, operate_method, cn_name, enable, title_expression)
values ('com.sitech.portal.system.service.impl.SysDeptServiceImpl', 'delete', '删除部门', '1', 'after.DEPT_NAME');
insert into TB_SYS_LOGGING_CFG (operate_class, operate_method, cn_name, enable, title_expression)
values ('com.sitech.portal.system.service.impl.SysDeptServiceImpl', 'add', '添加部门', '1', 'after.DEPT_NAME');
insert into TB_SYS_LOGGING_CFG (operate_class, operate_method, cn_name, enable, title_expression)
values ('com.sitech.portal.system.service.impl.SysLogInmsgServiceImpl', 'delete', '删除用户信息', '1', 'after.NAME');
insert into TB_SYS_LOGGING_CFG (operate_class, operate_method, cn_name, enable, title_expression)
values ('com.sitech.portal.system.service.impl.SysLogInmsgServiceImpl', 'update', '修改用户信息', '1', 'before.NAME-->after.NAME');
insert into TB_SYS_LOGGING_CFG (operate_class, operate_method, cn_name, enable, title_expression)
values ('com.sitech.portal.system.service.impl.SysLogInmsgServiceImpl', 'updateMenuType', null, null, null);
insert into TB_SYS_LOGGING_CFG (operate_class, operate_method, cn_name, enable, title_expression)
values ('com.sitech.portal.system.service.impl.SysLogInmsgServiceImpl', 'add', '添加用户', '1', 'after.NAME');
insert into TB_SYS_LOGGING_CFG (operate_class, operate_method, cn_name, enable, title_expression)
values ('com.sitech.portal.system.service.impl.SysModuleServiceImpl', 'update', '修改模块信息', '1', 'before.MODULE_NAME-->after.MODULE_NAME');
insert into TB_SYS_LOGGING_CFG (operate_class, operate_method, cn_name, enable, title_expression)
values ('com.sitech.portal.system.service.impl.SysModuleServiceImpl', 'deleteByPKs', '删除模块信息', '1', 'after.MODULE_NAME');
insert into TB_SYS_LOGGING_CFG (operate_class, operate_method, cn_name, enable, title_expression)
values ('com.sitech.portal.system.service.impl.SysModuleServiceImpl', 'add', '添加模块', '1', 'after.MODULE_NAME');
insert into TB_SYS_LOGGING_CFG (operate_class, operate_method, cn_name, enable, title_expression)
values ('com.sitech.portal.system.service.impl.SysParaServiceImpl', 'add', '添加系统参数', '1', 'after.PARA_NAME');
insert into TB_SYS_LOGGING_CFG (operate_class, operate_method, cn_name, enable, title_expression)
values ('com.sitech.portal.system.service.impl.SysParaServiceImpl', 'update', '修改系统参数', '1', 'before.PARA_NAME-->after.PARA_NAME');
insert into TB_SYS_LOGGING_CFG (operate_class, operate_method, cn_name, enable, title_expression)
values ('com.sitech.portal.system.service.impl.SysParaServiceImpl', 'deleteForTB_SYS_PARAByPK', '删除系统参数', '1', 'after.PARA_NAME');
insert into TB_SYS_LOGGING_CFG (operate_class, operate_method, cn_name, enable, title_expression)
values ('com.sitech.portal.system.service.impl.SysRoleModuleServiceImpl', 'deleteByModIDS', '修改角色模块关系', null, null);
insert into TB_SYS_LOGGING_CFG (operate_class, operate_method, cn_name, enable, title_expression)
values ('com.sitech.portal.system.service.impl.SysRoleModuleServiceImpl', 'deleteByRoleId', '修改角色模块关系', null, null);
insert into TB_SYS_LOGGING_CFG (operate_class, operate_method, cn_name, enable, title_expression)
values ('com.sitech.portal.system.service.impl.SysRoleServiceImpl', 'add', '添加角色', '1', 'after.ROLE_NAME');
insert into TB_SYS_LOGGING_CFG (operate_class, operate_method, cn_name, enable, title_expression)
values ('com.sitech.portal.system.service.impl.SysRoleServiceImpl', 'deleteRole', '删除角色', '1', 'after.ROLE_NAME');
insert into TB_SYS_LOGGING_CFG (operate_class, operate_method, cn_name, enable, title_expression)
values ('com.sitech.portal.system.service.impl.SysRoleServiceImpl', 'update', '修改角色信息', '1', 'before.ROLE_NAME-->after.ROLE_NAME');
insert into TB_SYS_LOGGING_CFG (operate_class, operate_method, cn_name, enable, title_expression)
values ('com.sitech.portal.system.service.impl.SysUserPanelOrderServiceImpl', 'updatePanelOrder', '调整panel排序', null, null);
insert into TB_SYS_LOGGING_CFG (operate_class, operate_method, cn_name, enable, title_expression)
values ('com.sitech.portal.system.service.impl.SysUserRoleServiceImpl', 'deleteByUserId', '解除用户角色关系', null, null);
insert into TB_SYS_LOGGING_CFG (operate_class, operate_method, cn_name, enable, title_expression)
values ('com.sitech.portal.system.service.impl.SysUserRoleServiceImpl', 'add', '添加用户角色关系', null, null);
insert into TB_SYS_LOGGING_CFG (operate_class, operate_method, cn_name, enable, title_expression)
values ('com.sitech.portal.system.service.impl.SysWorkGroupServiceImpl', 'deleteWorkGroup', '删除工作组', '1', 'after.WORKGROUP_NAME');
insert into TB_SYS_LOGGING_CFG (operate_class, operate_method, cn_name, enable, title_expression)
values ('com.sitech.portal.system.service.impl.SysWorkGroupServiceImpl', 'updateWorkGroup', '修改工作组信息', '1', 'before.WORKGROUP_NAME-->after.WORKGROUP_NAME');
insert into TB_SYS_LOGGING_CFG (operate_class, operate_method, cn_name, enable, title_expression)
values ('com.sitech.portal.system.service.impl.SysWorkGroupServiceImpl', 'deleteUserFromWorkGroup', '删除用户从工作组', '1', 'after.WORKGROUP_NAME');
commit;

  create table TB_SYS_ROLE_DEPT
(
  role_id VARCHAR2(36),
  dept_id VARCHAR2(50),
  user_id VARCHAR2(50)
);
alter table TB_SYS_MODULE add sso_type VARCHAR2(1);
-- Add comments to the columns 
comment on column TB_SYS_MODULE.sso_type
  is '链接外部系统方式，0正常，1 url中增加参数校验';
  create table TB_SYS_LOG_USERCHANGE
(
  from_loginid VARCHAR2(50) not null,
  to_loginid   VARCHAR2(50) not null,
  opttime      DATE not null
);

-- Add comments to the columns 
comment on column TB_SYS_LOG_USERCHANGE.from_loginid
  is '当前用户';
comment on column TB_SYS_LOG_USERCHANGE.to_loginid
  is '切换到的用户';
comment on column TB_SYS_LOG_USERCHANGE.opttime
  is '切换时间';
  alter table TB_SYS_LOGINMSG add is_vip VARCHAR2(2);
-- Add comments to the columns 
comment on column TB_SYS_LOGINMSG.is_vip
  is '1 白名单；非1 普通用户';
  create table TB_SYS_USER_WORKGROUP
(
  login_id     VARCHAR2(20) not null,
  workgroup_id VARCHAR2(100) not null
);


-- prompt
-- prompt Creating sequence DEFAUL_SEQ
-- prompt ============================
-- prompt
create sequence DEFAUL_SEQ
minvalue 1
maxvalue 9999999999999999999999999999
start with 431
increment by 1
nocache
order;

-- prompt
-- prompt Creating sequence ID_SYS_USERINFO
-- prompt =================================
-- prompt
create sequence ID_SYS_USERINFO
minvalue 1
maxvalue 9999999999999999999999999999
start with 4
increment by 1
nocache
order;

-- prompt
-- prompt Creating sequence SEQ_TB_SYS_4A_OPER_LOG
-- prompt ========================================
-- prompt
create sequence SEQ_TB_SYS_4A_OPER_LOG
minvalue 1
maxvalue 9999999999999999999999999999
start with 121
increment by 1
cache 20;

-- prompt
-- prompt Creating sequence SQ_SYS_CALENDAR
-- prompt =================================
-- prompt
create sequence SQ_SYS_CALENDAR
minvalue 1
maxvalue 9999999999999999999999999999
start with 8
increment by 1
nocache
order;

-- prompt
-- prompt Creating view V_OLD_TB_SYS_COMPANY
-- prompt ==================================
-- prompt
create or replace view v_old_tb_sys_company as
select dept_id comp_id, 
dept_name comp_name, 
dept_desc comp_desc, 
dept_code comp_code, 
area comp_type,manage_unit_id 
from tb_sys_dept 
where (super_dept_id='0'or super_dept_id is null)
/

-- prompt
-- prompt Creating view V_OLD_TB_SYS_DEPT
-- prompt ===============================
-- prompt
create or replace view v_old_tb_sys_dept as
select t.dept_id ,
t.dept_name ,
t.dept_desc ,
t.dept_code ,
t.area ,substr(t.super_dept_id,1,2) comp_id,
CASE WHEN t.super_dept_id=t.comp_id THEN null  ELSE t.super_dept_id END super_dept_id ,
t.MGR_ID,t.is_team,
t.Manage_unit_id,
t.dept_id_bak
from tb_sys_dept t 
where t.super_dept_id<>'0'
/

-- prompt
-- prompt Creating view V_TB_SYS_DEPT_4A
-- prompt ==============================
-- prompt
create or replace view v_tb_sys_dept_4a as
select t.dept_id id,t.dept_name name,t.dept_code code,t.DEPT_DESC DEPT_DESC,t.super_dept_id parentid,t.AREA AREA,t.MGR_ID MGRID,t.IS_TEAM IS_TEAM from tb_sys_dept t
/

-- prompt
-- prompt Creating view V_TB_SYS_LOGINMSG_4A
-- prompt ==================================
-- prompt
create or replace view v_tb_sys_loginmsg_4a as
select t.login_id USERID,
       t.name USERNAME,
       t.dept_id ORGID,
       t.email_addr EMAIL,
       t.login_phone MOBILE,
       t.login_passwd PASSWORD,
       CASE t.lockstate
         WHEN '0' THEN
          '1'
         ELSE
          '0'
       END STATUS
  from tb_sys_loginmsg t
/

-- prompt
-- prompt Creating view V_TB_SYS_MODULE_4A
-- prompt ================================
-- prompt
create or replace view v_tb_sys_module_4a as
select t.module_id id,t.module_name name,t.parent_id parentid,t.MODULE_ENABLE ENABLED from tb_sys_module t
/

-- prompt
-- prompt Creating view V_TB_SYS_ROLE_4A
-- prompt ==============================
-- prompt
create or replace view v_tb_sys_role_4a as
select t.role_id id,t.role_name name,t.parent_id parentid,t.ROLE_DESC ROLE_DESC from tb_sys_role t
/

-- prompt
-- prompt Creating view V_TB_SYS_ROLE_MODULE_4A
-- prompt =====================================
-- prompt
create or replace view v_tb_sys_role_module_4a as
select t.module_id moduleid,t.role_id roleid from tb_sys_role_module t
/

-- prompt
-- prompt Creating view V_TB_SYS_USER_ROLE_4A
-- prompt ===================================
-- prompt
create or replace view v_tb_sys_user_role_4a as
select t.login_id loginId,t.role_id roleid from tb_sys_user_role t
/

-- prompt
-- prompt Creating function FUN_MODULE_DIRECTORY
-- prompt ======================================
-- prompt
create or replace function Fun_module_directory(moduleId varchar2)
return varchar2 IS
       v_ename varchar2(1000); 
    cursor emp_cursor is
      select * from TB_SYS_MODULE t start with t.module_id = moduleId connect by prior t.parent_id = t.module_id;
      c_row  emp_cursor%rowtype;

    begin
  
     for c_row in emp_cursor 
     loop 
          v_ename := c_row.module_name || '/' ||v_ename;
     end loop; 
     
     return v_ename;

     end;
/

-- prompt
-- prompt Creating function FUN_USER_PANEL
-- prompt ================================
-- prompt
create or replace function Fun_user_panel(userId varchar2,panelIds varchar2)
return varchar2 
as
       v_panelId_col varchar2(200); 
       cols number;
       j number;
       i number;
       v_element varchar2(50);
       v_temp varchar2(100);
       v_id varchar2(50);

    begin
         v_temp := panelIds;
         cols := 0;
         while instr(v_temp, ';') > 0
          loop
              v_element := substr(v_temp,1,instr(v_temp, ';')-1);
              v_temp := substr(v_temp, instr(v_temp,';')+ length(';') , length(v_temp));
              cols := cols+1;
         end loop;
         if cols>0
         then
                   cols := cols+1;
         end if;

         
         v_temp := panelIds;
         j:=0;
          while instr(v_temp, ';')>=0
            loop
                if instr(v_temp, ';') > 0
                then
                  v_element := substr(v_temp,1,instr(v_temp, ';')-1);
                  v_temp := substr(v_temp, instr(v_temp,';')+ length(';') , length(v_temp));
                else
                  v_element := v_temp;
                  v_temp := '';
                end if;
                
                v_panelId_col := v_element;
                i := 0;
                while instr(v_panelId_col, ',') >= 0
                  loop
                      if instr(v_panelId_col, ',') > 0
                      then
                          v_id := substr(v_panelId_col,1,instr(v_panelId_col, ',')-1);
                          v_panelId_col := substr(v_panelId_col, instr(v_panelId_col,',')+ length(',') , length(v_panelId_col));
                      else
                          v_id := v_panelId_col;
                          v_panelId_col := '';
                      end if;
        
                      insert into TB_SYS_USERPANEL_ORDER(LOGIN_ID,PANEL_ID,PANEL_ORDER) values(userId,v_id,j+i*cols);
                      i := i+1;
                 end loop;
                j := j+1;
                
      

           end loop;

           return '';
     end;
/

-- prompt
-- prompt Creating procedure P_COMPANY_DEPT
-- prompt =================================
-- prompt
create or replace procedure p_COMPANY_dept
is
m_direct varchar2(500); 
cursor var_c is select * from TB_SYS_COMPANY; 
r_COMPANY var_c %ROWTYPE; 
begin 
open var_c; 
    loop
    fetch var_c 
        into r_COMPANY; 
        EXIT WHEN var_c%NOTFOUND; 
          begin
               m_direct := r_COMPANY.COMP_ID;
               insert into tb_sys_dept(DEPT_ID,DEPT_NAME,DEPT_DESC,DEPT_CODE,COMP_ID,SUPER_DEPT_ID,area)
               values(r_COMPANY.COMP_ID,r_COMPANY.COMP_NAME,r_COMPANY.COMP_DESC,r_COMPANY.COMP_CODE,'0','0',r_COMPANY.COMP_TYPE);
               update tb_sys_dept set SUPER_DEPT_ID=r_COMPANY.COMP_ID where SUPER_DEPT_ID is null and COMP_ID=r_COMPANY.COMP_ID;
          end;
    end loop; 
    close var_c; 

end;
/

-- prompt
-- prompt Creating procedure P_MODULE_DIRECTORY
-- prompt =====================================
-- prompt
create or replace procedure p_module_directory
is
m_direct varchar2(500); 
cursor var_c is select * from TB_SYS_MODULE where MODULE_DIRECTORY is null; 
r_module var_c %ROWTYPE; 
begin 
open var_c; 
    loop
    fetch var_c 
        into r_module; 
        EXIT WHEN var_c%NOTFOUND; 
          begin
               m_direct := Fun_module_directory(r_module.module_id);
               update TB_SYS_MODULE set MODULE_DIRECTORY=m_direct where module_id=r_module.module_id;
          end;
    end loop; 
    close var_c; 

end;
/

-- prompt
-- prompt Creating procedure P_USER_PANEL
-- prompt ===============================
-- prompt
create or replace procedure p_user_panel
is
resultstr varchar2(100);
cursor var_c is select distinct * from tb_sys_portal_usersetting; 
r_panel var_c %ROWTYPE; 
begin 
open var_c; 
    loop
    fetch var_c 
        into r_panel; 
        EXIT WHEN var_c%NOTFOUND; 
          begin
               resultstr := fun_user_panel(r_panel.login_id,r_panel.porlets);
          end;
    end loop; 
    close var_c; 

end;
/

-- prompt
-- prompt Creating trigger T_TB_SYS_COMPANY
-- prompt =================================
-- prompt
Create Or Replace Trigger t_tb_sys_company
  Before insert or update or delete On tb_sys_company FOR EACH ROW
Begin
  if updating then
    update tb_sys_dept set DEPT_NAME=:NEW.COMP_NAME,DEPT_DESC=:NEW.COMP_DESC,DEPT_CODE=:NEW.COMP_CODE,AREA=:NEW.COMP_TYPE where DEPT_ID=:NEW.COMP_ID;
  elsif deleting then
    delete from tb_sys_dept where DEPT_ID=:old.COMP_ID;
  elsif inserting then
    insert into tb_sys_dept(DEPT_ID,DEPT_NAME,DEPT_DESC,DEPT_CODE,AREA,COMP_ID,SUPER_DEPT_ID,IS_TEAM) values(:NEW.COMP_ID,:NEW.COMP_NAME,:NEW.COMP_DESC,:NEW.COMP_CODE,:NEW.COMP_TYPE,'0','0','0');
  end if;
End;
/


-- spool off
